package com.scalar.chiptrack.adaptor.ikanos.sales;

import java.io.File;
import java.io.IOException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Calendar;
import java.util.TimeZone;
import java.util.Locale;
import java.util.GregorianCalendar;

import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;
import jxl.CellType;
import jxl.DateCell;
import jxl.read.biff.BiffException;

import com.scalar.ScalarException;
import com.scalar.chiptrack.adaptor.AdaptorUtils;
import com.scalar.chiptrack.adaptor.dao.AdaptorRunInfo;
import com.scalar.chiptrack.adaptor.utils.Parser;
import com.scalar.chiptrack.adaptor.utils.ParserFactory;
import com.scalar.chiptrack.adaptor.utils.ParserResult;
import com.scalar.chiptrack.adaptor.utils.XLSParserImpl;
import com.scalar.chiptrack.commons.Address;
import com.scalar.chiptrack.commons.Constants;
import com.scalar.chiptrack.db.DBTransaction;
import com.scalar.chiptrack.db.DBXException;
import com.scalar.chiptrack.jobs.JobHandler;
import com.scalar.chiptrack.sales.parser.SalesParserDAO;
import com.scalar.chiptrack.sales.parser.SalesParserLineItem;
import com.scalar.chiptrack.users.notification.dao.NotificationDAO;
import com.scalar.chiptrack.utils.ConfigManager;
import com.scalar.chiptrack.utils.Logger;
import com.scalar.chiptrack.utils.Mailer;
import com.scalar.chiptrack.utils.StringUtils;

/**
 * User: Sujan
 * Date: Aug 1, 2008
 * Time: 4:32:56 PM
 */
public class SalesParser extends JobHandler
{
    private Logger m_parserLogger   = null;
    private String m_dataSourceDir  = null;
    private HashMap<String, SalesParserLineItem> m_lineItemMap   = new HashMap<String, SalesParserLineItem>();
    private final String BACKLOG_SHEET = "Daily Backlog";
    private final String PENDING_SHIPMENT_SHEET = "Pending Ship-Confirm";

    public SalesParser() throws Exception
    {
        try
        {
            m_dataSourceDir = ConfigManager.getSalesBacklogPortingFileDir() ;
            if ( m_dataSourceDir != null )
            {
                AdaptorUtils.makeAllAdaptorDirs( m_dataSourceDir );

                m_parserLogger = AdaptorUtils.getDataParserLogger( m_dataSourceDir, "salesBacklog" );
                AdaptorUtils.setLogger( m_parserLogger );
            }
        }
        catch ( Exception e )
        {
            e.printStackTrace();
        }
    }
    // To be implemented by sub classes
    public void execute() throws Exception
    {
        boolean success = false;
        AdaptorUtils.printHeader("SALES BACKLOG PORTING", CONSTANT_PARSER );

        ArrayList<File> successFileList      = new ArrayList<File>();
        File doneDestDir = new File( m_dataSourceDir + "/done" );
        File dataoutDir  = new File( m_dataSourceDir );

        File[] fileList = dataoutDir.listFiles();

        //Arrays.sort( fileList, new FileNameDateComparator() );

        if ( ( fileList == null ) || ( fileList.length <= 3 ) )
        {
            log( "No file found in directory: " + dataoutDir );
            throw new ScalarException( "\n+-----------No file found in directory " + dataoutDir + "-----\n\n" );
        }

        for ( int p = 0; p < fileList.length; p++ )
        {
            if ( fileList[p].isDirectory() || !fileList[p].getName().startsWith( FILE_NAME_SALESBACKLOG_STARTS_WITH ) )
            {
                continue;
            }
            log ( "Parsing Sales backlog File:"  + fileList[p].getName() );
            System.out.println ( "Parsing Sales Backlog File:"  + fileList[p].getName());

            ArrayList<SalesParserLineItem> lineItemList = parseSalesFile ( fileList[p], BACKLOG_SHEET );
            ArrayList<SalesParserLineItem> pendingShipmentlineItemList = parseSalesFile ( fileList[p], PENDING_SHIPMENT_SHEET );
            
            if( pendingShipmentlineItemList != null && pendingShipmentlineItemList.isEmpty() == false )
            {
            	lineItemList.addAll( pendingShipmentlineItemList );
            }

            log ( "Parsing completed successfully..." );
            System.out.println ( "Parsing completed successfully.....");
            //Collections.sort( lineItemList, new SalesParserLineItemComparator() );
            log ( "Porting Sales Backlog info..." );
            System.out.println ( "Porting Sales Backlog info..." );
            DBTransaction dbTransaction = null;
            String portingStatusMsg = null;
            try
            {
                dbTransaction = DBTransaction.getInstance();
                dbTransaction.begin();
                SalesParserDAO.saveSalesOrders( dbTransaction, lineItemList );
                if ( dbTransaction != null )
                {
                    dbTransaction.commit();
                }

                success = true;
            }
            catch ( ScalarException e )
            {
                log ( "Exception while porting Sales Backlog Orders:" + e.getRootCause() );
                throw e;
            }
            finally
            {
                if ( dbTransaction != null )
                {
                    if ( !success )
                    {
                        try { dbTransaction.rollback(); } catch ( DBXException ignore ){}
                    }
                    dbTransaction = null;
                }
            }


            if ( success ) //SEND PORTING STATUS MAIL
            {
                log ( "Porting Sales Backlog completed successfully...." );
                System.out.println ( "Porting Sales Backlog completed successfully...." );
                log ( "Sending porting status mail..." );
                portingStatusMsg = preparePortingStatusMsg(lineItemList);
                try
                {
                    StringBuffer msg = new StringBuffer ( );
                    msg.append ( "<HTML><HEAD><TITLE>Sales Order Porting Status</TITLE></HEAD>" );
                    msg.append ( "<BODY ALIGN='CENTER'><TABLE STYLE='FONT-SIZE: 8PT; COLOR: BLACK; FONT-FAMILY: VERDANA' BORDERCOLOR='#FFFFFF' CELLSPACING='1' CELLPADDING='3' BORDER=0 ALIGN='CENTER'>" );
                    msg.append ( "<TR BGCOLOR='#CCCCFF'><TD COLSPAN='8' ALIGN='CENTER'><B>SALES ORDER PORTING STATUS REPORT</B></TD></TR>" );
                    msg.append ( "<TR BGCOLOR='#D3D3D6' ALIGN='CENTER'>" );
                    msg.append ( "<TD><B>S.No</B></TD><TD><B>SALES ORDER #</B></TD><TD><B>CUSTOMER NAME</B></TD><TD><B>LINE ITEM #</B></TD><TD><B>SHIPMENT #</B></TD><TD><B>PRODUCT</B></TD><TD><B>PORTING STATUS</B></TD><TD><B>REMARKS</B></TD>" );
                    msg.append ( "</TR>" );
                    msg.append ( portingStatusMsg );
                    msg.append ( "</TABLE></BODY></HTML>" );
                    String fromEmail = ConfigManager.getScalarAdminEmail();
                    sendSalesOrdersPortingAlert( "Sales Order porting status alert", msg.toString(), fromEmail, Constants.SALES_ORDER_PORTING_NOTIFICATION );
                    log ( "Porting status mail sent successfully" );
                    System.out.println ( "Porting status mail sent successfully" );
                }
                catch ( ScalarException e )
                {
                    log ( "**WARNING** Could not send Porting Status alert mail:" + e.getRootCause() );
                    System.out.println ( "***WARNING*** Could not send Porting Status alert mail" );
                    e.printStackTrace();
                }
            }
            else
            {
                log ( "Unable to port the customer information...." );
                System.out.println ( "Unable to port the customer information..." );
            }
            

             //TO PARSE THER SHIPMENT SHEET
            parseShippingFile ( fileList[p], "Shipment report" );
            //TO PARSE PENDING SHIP CONFIRM SHEET
            //parseShippingFile ( fileList[p], "Pending Ship-Confirm" );

            successFileList.add( fileList[p] );
        }
        try
        {
            if( !m_lineItemMap.isEmpty() )
            {
                System.out.println(" Saving Customer shipping info..");
                SalesParserDAO.saveSalesShippedOrders( null , m_lineItemMap.values().iterator() );
                success = true;
            }
        }
        catch (Exception e )
        {
            success = false;
            System.out.println("!!ERROR..While saving the Sales Orders Shipping details" + e);
            log("!!ERROR..While saving the Sales Orders Shipping details" + e);
            e.printStackTrace();
            throw e;
        }
        int adaptor_id = AdaptorRunInfo.getLastRunAdaptorId(SalesBacklogPOP3Adaptor.ADAPTOR_NAME, CONSTANT_ADAPTOR);

        if ( success )
        {
            AdaptorRunInfo.saveAdaptorLastUpdatedStatus(CONSTANT_PARSER, SalesBacklogPOP3Adaptor.ADAPTOR_NAME, CONSTANT_SUCCESS, adaptor_id);
            System.out.println( "SUCCESSFULLY UPDATED IN THE DATABASE" );
        }
        if ( success )
        {
            AdaptorUtils.moveFilesToDone( successFileList , doneDestDir );
        }
    }

    private void log( String message )
    {
        if ( message == null ) return;

        if ( m_parserLogger != null )
        {
            m_parserLogger.logDebug( message + LINE_SEPARATOR );
        }
        else
        {
            System.out.println( message );
        }
    }

   /* private File getFileForPorting ( String salesPortingFile ) throws ScalarException
    {
        if ( salesPortingFile == null || salesPortingFile.trim().length() == 0 )
        {
            log ( "Sales Porting File Name not specified" );
            System.out.println ( "Sales Porting File Name not specified" );
            throw new ScalarException( "Sales Porting File Name not specified" );
        }
        File portingFile = new File ( salesPortingFile );
        if ( portingFile.exists() == false && portingFile.isFile() == false )
        {
            log ( "Sales Porting File:" + salesPortingFile + " Does not exist" );
            throw new ScalarException( salesPortingFile + " does not exist" );
        }
        else
        {
            log ( "Sales Porting File:" + salesPortingFile + " Found" );
            System.out.println ( "Sales Porting File:" + salesPortingFile + " Found" );
        }
        return portingFile;
    } */
    private ArrayList<SalesParserLineItem> parseSalesFile ( File salesPortingFile, String sheetName ) throws Exception
    {
        if ( salesPortingFile == null ) throw new ScalarException( "Filename argument is null" );

        ParserResult result            = null;
        SalesParserLineItem lineItem = null;
        String shipLocation 		= null;
        ArrayList<SalesParserLineItem> lineItemList      = new ArrayList<SalesParserLineItem>();

        Parser parser = ParserFactory.getXLSParser( salesPortingFile, sheetName );

        ((XLSParserImpl)parser).setHeaderRowIdx(2);
        ((XLSParserImpl)parser).setDataStartRowIdx(3);

        log( "\nFound " + salesPortingFile.getAbsolutePath() );
        log( "\nParsing the file for Sales Back Log..." );
        System.out.println( "\nFound " + salesPortingFile.getAbsolutePath() );
        System.out.println( "\nParsing the file for Sales back Log..." );

        result = parser.parse();

		if( result == null )
		{
			log("\nSheet could not be found " + sheetName );
			throw new ScalarException("Sheet Could not be found " + sheetName );
		}
        //System.out.println("result.getHeadings() = " + result.getHeadings());
        while ( result.next() )
        {
            if (  result.getString("Sales Order") != null && result.getString("Sales Order").trim().length() > 0 )
            {
                lineItem = new SalesParserLineItem();

                if (result.getString("Customer Name") != null && result.getString("Customer Name").trim().length() > 0 && !"#N/A".equalsIgnoreCase(result.getString("Customer Name") ) )
                {
                     lineItem.setCustomerName( result.getString("Customer Name").trim() );
                }
                else
                {
                     lineItem.setPortingStatusCode( -1 );
                     lineItem.setRemarks( "<B>Customer Name</B> Missing<BR>" );
                     log( "**NOT PORTED**Customer Name Missing for the row:" + (result.getRowCount()) );
                }
                if (result.getString("Hold Flag") != null && "Yes".equalsIgnoreCase(result.getString("Hold Flag").trim() ) )
                {
                     lineItem.setStatus( "HOLD" );
                }
	
                lineItem.setSalesOrderNo( result.getString("Sales Order") != null ? result.getString("Sales Order").trim() : null );
                
                if( result.getString( "Operating Unit" ) != null && "Ikanos Communications, Inc".equalsIgnoreCase( result.getString( "Operating Unit" ) ) )
                {
                	lineItem.setShippingLocation( result.getString("Ship From Location") != null ? result.getString("Ship From Location").trim() : null );
                }
                else
                {
                	if( result.getString("Ship Priority") != null )
                	{
                		shipLocation = result.getString("Ship Priority");
                		
                		if( shipLocation.indexOf( "-" ) > 0 )
                		{
                			shipLocation = shipLocation.substring( 0 , shipLocation.indexOf( "-" ) );
                		}
                	}

                	
                	lineItem.setShippingLocation(  shipLocation );
                }
                lineItem.setMarketingPartNo( result.getString("Ordered Item") != null ? result.getString("Ordered Item").trim() : null );
                lineItem.setManufacturingPartNo( result.getString("Configured Item") != null ? result.getString("Configured Item").trim() : null );
                try
                {   if ( result.getString("Backlog Qty") != null && result.getString("Backlog Qty").trim().length() > 0 )
                    {
                        lineItem.setQuantity ( Integer.parseInt(result.getString("Backlog Qty").replaceAll(",","") ) );
                    }
                }
                catch ( NumberFormatException e )
                {
                    lineItem.setPortingStatusCode( -1 );
                    lineItem.setRemarks( lineItem.getRemarks() + " unable to parse <B>Backlog Qty</B> :"+ e.getMessage()+ "<BR>" );
                    log( "**NOT PORTED** Backlog Qty parse exception for the row:" + (result.getRowCount())  + ":" + e.getMessage() );
                    //continue;
                }

                lineItem.setRequestedDockDate((result.getString("Request Date") != null && !"NULL".equalsIgnoreCase(result.getString("Request Date").trim() ) ) ? StringUtils.formatDateToString(result.getDate("Request Date")) : null );
                lineItem.setScheduleDate( (result.getString("Promise Date") != null && !"NULL".equalsIgnoreCase(result.getString("Promise Date").trim() ) ) ? StringUtils.formatDateToString(result.getDate("Promise Date")) : null );
                lineItem.setRevisedScheduleDate( (result.getString("Schd Ship Date") != null && !"NULL".equalsIgnoreCase(result.getString("Schd Ship Date").trim() ) ) ? StringUtils.formatDateToString(result.getDate("Schd Ship Date")) : null );
                lineItem.setEndCustomer( result.getString("OEM") != null && !"NULL".equalsIgnoreCase(result.getString("OEM") ) ? result.getString("OEM").trim() : null );
                lineItem.setBookingDate( (result.getString("Ordered Date") != null && !"NULL".equalsIgnoreCase(result.getString("Ordered Date").trim() ) ) ? StringUtils.formatDateToString(result.getDate("Ordered Date")) : null );
				lineItem.setShipReqNo( result.getString("Ship Req No") );

                if ( result.getString("Order Type") != null && ( "STD".equalsIgnoreCase(result.getString("Order Type") ) || "STD SG".equalsIgnoreCase(result.getString("Order Type") ) ) )
                {
                     lineItem.setIsSample( 1 );
                }
                else
                {
                    lineItem.setIsSample( 0 );
                }

                try {
                lineItem.setUnitPrice( result.getString("Selling Price") != null ? Float.parseFloat( result.getString("Selling Price").trim().replaceAll("\"", "" ).replaceAll("\\$","").replaceAll(",","") ) : null );
                }
                catch ( NumberFormatException e )
                {
                    log( "**WARNING**Got exception for Price for the row:" + result.getRowCount() + ":" + e.getMessage() );
                }
                
                /*try
                {
                    lineItem.setTotalAmt ( result.getString("Extended Price") != null ? Double.parseDouble( result.getString("Extended Price").trim().replaceAll("\"", "" ).replaceAll("\\$","").replaceAll(",","") ) : null );
                }
                catch ( NumberFormatException e )
                {
                    log( "**WARNING**Got exception for Extended Price for the row:" + result.getRowCount() + ":" + e.getMessage() );
                } */

                lineItem.setSalesPerson(result.getString("Salesrep") != null ? result.getString("Salesrep").trim() : null );
                lineItem.setCustomerNumber( result.getString("Customer Number") != null ? result.getString("Customer Number").trim() : null );
                lineItem.setCustomerPO( result.getString("Cust PO Number") != null ? result.getString("Cust PO Number").trim() : null );
                try
                {
                    lineItem.setInternalPOLineNo( result.getString("Cust PO Line") != null ? result.getInt("Cust PO Line") : 1 );
                }
                catch ( NumberFormatException e )
                {
                    lineItem.setInternalPOLineNo(1 );
                }
                lineItem.setCustomerPartNo(result.getString("Customer Item")  != null && !"NULL".equalsIgnoreCase(result.getString("Customer Item")) ? result.getString("Customer Item").trim() : null );
                lineItem.setDeliveryTerms( result.getString("Freight Terms Code") != null ? result.getString("Freight Terms Code").trim() : null );
                lineItem.setRegion( result.getString("Sales Region") != null ? result.getString("Sales Region").trim() : null );
                Address shipAddress = new Address();
                shipAddress.setAddress1( lineItem.getCustomerName() );
                shipAddress.setAddress2(result.getString("Ship Address1") );
                shipAddress.setAddress3(result.getString("Ship Address2") );
                shipAddress.setCity( result.getString("Ship City") );
                shipAddress.setState( result.getString("Ship State") );
                shipAddress.setZipCode( result.getString("Ship Postal Code") );
                shipAddress.setCountry( result.getString("Ship Cntry") );
                shipAddress.setDefaultAddress( "Y".equalsIgnoreCase( result.getString("Ship Primary") ) ? "true" : "false" );

                lineItem.setShipAddress( shipAddress );
                lineItem.setDescription(result.getString("Item Desc"));
                lineItem.setShippingMethod(result.getString("Ship Method"));
                lineItem.setPaymentTerms(result.getString("Payment Term"));
                try
                {
                    lineItem.setLineItemNo( Integer.parseInt( result.getString("Line Number") ) );
                }
                catch ( NumberFormatException e )
                {
                    lineItem.setPortingStatusCode( -1 );
                    lineItem.setRemarks( lineItem.getRemarks() + " unable to parse <B>Line Item Number</B> :"+ e.getMessage()+ "<BR>" );
                    log( "**NOT PORTED** Line Item Number for the row:" + result.getRowCount()  + ":" + e.getMessage() );
                    //continue;
                }
                try
                {
                    lineItem.setShipmentNo( Integer.parseInt( result.getString("Ship Number") ) );
                }
                catch ( NumberFormatException e )
                {
                    lineItem.setPortingStatusCode( -1 );
                    lineItem.setRemarks( lineItem.getRemarks() + " unable to parse <B>Line Item Shipment Number</B> :"+ e.getMessage()+ "<BR>" );
                    log( "**NOT PORTED** Line Item Shipment Number for the row:" + result.getRowCount()  + ":" + e.getMessage() );
                    //continue;
                }

                if ( PENDING_SHIPMENT_SHEET.equalsIgnoreCase(sheetName ) )
                {
                    lineItem.setPendingShipment( true );
                }
                
                if( result.getString( "Operating Unit" ) != null && !"Ikanos Communications (Cayman) Ltd.".equalsIgnoreCase( result.getString( "Operating Unit" ) ) )
                {
                	lineItemList.add(lineItem);
                }
            }
        }
        return lineItemList;
    }
    
    private ArrayList<SalesParserLineItem> parseSalesFileOld ( File salesPortingFile, String sheetName ) throws IOException, BiffException
    {
        Workbook workbook 	        = Workbook.getWorkbook( salesPortingFile );
        Sheet sheet 		        = workbook.getSheet( sheetName );
        Cell[] cells 			    = null;
        SalesParserLineItem lineItem = null;
        ArrayList<SalesParserLineItem> lineItemList      = new ArrayList<SalesParserLineItem>();
        int noOfCells               = 0;
        int noOfRows                = sheet.getRows();
        for ( int rowCount = 3; rowCount < noOfRows; rowCount++ )
        {
            cells = sheet.getRow( rowCount );
            noOfCells = cells.length;
            if ( noOfCells > 0  )
            {
                lineItem = new SalesParserLineItem();
                if ( cells[0].getContents() != null && cells[0].getContents().trim().length() > 0 && !"#N/A".equalsIgnoreCase(cells[0].getContents().trim()) ) //Customer available
                {
                    lineItem.setCustomerName(cells[0].getContents().trim());
                }
                else
                {
                    lineItem.setPortingStatusCode( -1 );
                    lineItem.setRemarks( "<B>Customer Name</B> Missing<BR>" );
                    log( "**NOT PORTED**Customer Name Missing for the row:" + (rowCount+1) );
                    //continue;
                }


                if ( noOfCells > 1 && cells[1].getContents() != null && "Yes".equalsIgnoreCase( cells[1].getContents().trim() ) ) //Hold Flag
                {
                    lineItem.setStatus( "HOLD" );
                }
                /*else
                {
                    log( "**NOT PORTED**Shipping Location Missing for the row:" + (rowCount+1) );
                    continue;
                }*/
                if ( noOfCells > 2 && cells[2].getContents() != null ) //Sales Order
                {
                    lineItem.setSalesOrderNo(cells[2].getContents().trim());
                }
               /* if ( noOfCells > 3 && cells[3].getContents() != null ) //Order Line
                {
                    String[] split = cells[3].getContents().trim().split(".");
                    try
                    {
                        lineItem.setLineItemNo( Integer.parseInt( split[0] ) );
                        lineItem.setShipmentNo( Integer.parseInt( split[1] ) );
                    }
                    catch ( NumberFormatException e )
                    {
                        log( "**NOT PORTED** Line Item Number for the row:" + (rowCount+1)  + ":" + e.getMessage() );
                        continue;
                    }
                }*/
                if ( noOfCells > 4 && cells[4].getContents() != null ) //Ship From Location
                {
                    lineItem.setShippingLocation(cells[4].getContents().trim());
                }
                if ( noOfCells > 5 && cells[5].getContents() != null ) //Ship Req No
                {
                    lineItem.setShipReqNo( cells[5].getContents().trim() );
                    //IGNORE
                }
                if ( noOfCells > 6 && cells[6].getContents() != null ) //Ordered Item
                {
                    lineItem.setMarketingPartNo( cells[6].getContents().trim() );
                }
                if ( noOfCells > 7 && cells[7].getContents() != null ) //Configured Item
                {
                    lineItem.setManufacturingPartNo(cells[7].getContents().trim());
                }
                if ( noOfCells > 8 && cells[8].getContents() != null ) //Backlog Qty
                {
                    int qty = 0;
                    try
                    {
                        if ( cells[8].getContents().indexOf(".") != -1 )
                        {
                            qty = Integer.parseInt ( cells[8].getContents().trim().substring(0, cells[8].getContents().indexOf(".")).replaceAll( ",", "" ) );
                        }
                        else
                        {
                           qty = Integer.parseInt ( cells[8].getContents().trim().replaceAll( ",", "" ) );
                        }
                        lineItem.setQuantity( qty );
                    }
                    catch ( NumberFormatException e )
                    {
                        lineItem.setPortingStatusCode( -1 );
                        lineItem.setRemarks( lineItem.getRemarks() + " unable to parse <B>Backlog Qty</B> :"+ e.getMessage()+ "<BR>" );
                        log( "**NOT PORTED** Backlog Qty parse exception for the row:" + (rowCount+1)  + ":" + e.getMessage() );
                        //continue;
                    }
                }
                if ( noOfCells > 9 && cells[9].getContents() != null ) //Backorder Qty
                {
                    //IGNORE
                }
                if ( noOfCells > 10 && cells[10].getContents() != null ) //Request Date
                {
                    if( cells[10].getType().equals( CellType.DATE ) )
                    {
                        DateCell dc = (DateCell) cells[10];
                        java.util.Date reqDate = null;
                        reqDate = dc.getDate();
                        if ("America/Los_Angeles".equals(System.getProperty("user.timezone")))
                        {
                            Calendar cal = Calendar.getInstance(TimeZone.getTimeZone("GMT"), Locale.US);
                            cal.setTime(reqDate);
                            reqDate = new GregorianCalendar(cal.get(Calendar.YEAR), cal.get(Calendar.MONTH), cal.get(Calendar.DATE)).getTime();
                            lineItem.setRequestedDockDate(StringUtils.formatDateToString(reqDate));
                        }
                        else
                        {
                            Calendar cal = new GregorianCalendar();
                            cal.setTime(reqDate);
                            reqDate = new GregorianCalendar(cal.get(Calendar.YEAR), cal.get(Calendar.MONTH), cal.get(Calendar.DATE)).getTime();
                            lineItem.setRequestedDockDate(StringUtils.formatDateToString(reqDate));
                        }
                    }
                    else
                    {
                        String dt =  cells[10].getContents().trim().length() > 0 ? cells[10].getContents().trim() : null;

                        lineItem.setRequestedDockDate( StringUtils.formatDate(dt, "dd-MMM-yyyy", "MM/dd/yyyy") );
                    }
                    /*double totalAmt = 0d;
                    try { totalAmt = Double.parseDouble( cells[10].getContents().trim() ); } catch ( NumberFormatException ignore ) {}
                    lineItem.setTotalAmt( totalAmt );*/
                }
                if ( noOfCells > 11 && cells[11].getContents() != null ) //Promise Date
                {
                    if( cells[11].getType().equals( CellType.DATE ) )
                    {
                        DateCell dc = (DateCell) cells[11];
                        java.util.Date reqDate = null;
                        reqDate = dc.getDate();
                        if ("America/Los_Angeles".equals(System.getProperty("user.timezone")))
                        {
                            Calendar cal = Calendar.getInstance(TimeZone.getTimeZone("GMT"), Locale.US);
                            cal.setTime(reqDate);
                            reqDate = new GregorianCalendar(cal.get(Calendar.YEAR), cal.get(Calendar.MONTH), cal.get(Calendar.DATE)).getTime();
                            lineItem.setScheduleDate(StringUtils.formatDateToString(reqDate));
                        }
                        else
                        {
                            Calendar cal = new GregorianCalendar();
                            cal.setTime(reqDate);
                            reqDate = new GregorianCalendar(cal.get(Calendar.YEAR), cal.get(Calendar.MONTH), cal.get(Calendar.DATE)).getTime();
                            lineItem.setScheduleDate(StringUtils.formatDateToString(reqDate));
                        }
                    }
                    else
                    {
                        String dt = ( cells[11].getContents().trim().length() > 0 && !cells[11].getContents().trim().equalsIgnoreCase("NULL") ) ? cells[11].getContents().trim() : null;
                        lineItem.setScheduleDate( StringUtils.formatDate(dt, "dd-MMM-yyyy", "MM/dd/yyyy") );
                    }
                }
                if ( noOfCells > 12 && cells[12].getContents() != null ) //Schd Ship Date
                {
                    if( cells[12].getType().equals( CellType.DATE ) )
                    {
                        DateCell dc = (DateCell) cells[12];
                        java.util.Date reqDate = null;
                        reqDate = dc.getDate();
                        if ("America/Los_Angeles".equals(System.getProperty("user.timezone")))
                        {
                            Calendar cal = Calendar.getInstance(TimeZone.getTimeZone("GMT"), Locale.US);
                            cal.setTime(reqDate);
                            reqDate = new GregorianCalendar(cal.get(Calendar.YEAR), cal.get(Calendar.MONTH), cal.get(Calendar.DATE)).getTime();
                            lineItem.setRevisedScheduleDate(StringUtils.formatDateToString(reqDate));
                        }
                        else
                        {
                            Calendar cal = new GregorianCalendar();
                            cal.setTime(reqDate);
                            reqDate = new GregorianCalendar(cal.get(Calendar.YEAR), cal.get(Calendar.MONTH), cal.get(Calendar.DATE)).getTime();
                            lineItem.setRevisedScheduleDate(StringUtils.formatDateToString(reqDate));
                        }
                    }
                    else
                    {
                        String dt = ( cells[12].getContents().trim().length() > 0 && !cells[12].getContents().trim().equalsIgnoreCase("NULL") ) ? cells[12].getContents().trim() : null;
                        lineItem.setRevisedScheduleDate( StringUtils.formatDate(dt, "dd-MMM-yyyy", "MM/dd/yyyy") );
                    }
                }
                if ( noOfCells > 13 && cells[13].getContents() != null && !"NULL".equalsIgnoreCase( cells[13].getContents().trim() ) ) //OEM
                {
                    lineItem.setEndCustomer( cells[13].getContents().trim() );
                }
                if ( noOfCells > 14 && cells[14].getContents() != null ) //Carrier
                {
                    //todo..
                }
                if ( noOfCells > 15 && cells[15].getContents() != null ) //Order Status
                {
                   //IGNORE
                }
                if ( noOfCells > 16 && cells[16].getContents() != null ) //Line Status
                {
                   //IGNORE
                }
                if ( noOfCells > 17 && cells[17].getContents() != null ) //Ordered Date
                {
                    if( cells[17].getType().equals( CellType.DATE ) )
                    {
                        DateCell dc = (DateCell) cells[17];
                        java.util.Date reqDate = null;
                        reqDate = dc.getDate();
                        if ("America/Los_Angeles".equals(System.getProperty("user.timezone")))
                        {
                            Calendar cal = Calendar.getInstance(TimeZone.getTimeZone("GMT"), Locale.US);
                            cal.setTime(reqDate);
                            reqDate = new GregorianCalendar(cal.get(Calendar.YEAR), cal.get(Calendar.MONTH), cal.get(Calendar.DATE)).getTime();
                            lineItem.setBookingDate(StringUtils.formatDateToString(reqDate));
                        }
                        else
                        {
                            Calendar cal = new GregorianCalendar();
                            cal.setTime(reqDate);
                            reqDate = new GregorianCalendar(cal.get(Calendar.YEAR), cal.get(Calendar.MONTH), cal.get(Calendar.DATE)).getTime();
                            lineItem.setBookingDate(StringUtils.formatDateToString(reqDate));
                        }
                    }
                    else
                    {
                        String dt = cells[17].getContents().trim().length() > 0 ? cells[17].getContents().trim() : null;
                        lineItem.setBookingDate( StringUtils.formatDate(dt, "dd-MMM-yyyy", "MM/dd/yyyy") );
                    }
                }
                if ( noOfCells > 18 && cells[18].getContents() != null  && "STD".equalsIgnoreCase( cells[18].getContents().trim() ) ) //Order Type consider only STD
                {
                    lineItem.setIsSample( 1 );
                }
                else
                {
                	lineItem.setIsSample( 0 );// Using this field as consignment order because this order must be available in backlog but not in Left to book report
                    //continue;
                }
                if ( noOfCells > 19 && cells[19].getContents() != null ) //Line Type
                {
                    //IGNORE
                }
                if ( noOfCells > 20 && cells[20].getContents() != null ) //Platform
                {
                    //IGNORE
                }
                if ( noOfCells > 21 && cells[21].getContents() != null ) //Prod Grp
                {
                    //IGNORE
                }
                if ( noOfCells > 22 && cells[22].getContents() != null ) //Chip Fam
                {
                    //IGNORE
                }
                if ( noOfCells > 23 && cells[23].getContents() != null ) //Cancelled Qty
                {
                    //IGNORE
                }
                if ( noOfCells > 24 && cells[24].getContents() != null ) //Selling Price
                {
                    try
                    {
                        lineItem.setUnitPrice( Float.parseFloat( cells[24].getContents().trim().replaceAll("\"", "" ).replaceAll("\\$","").replaceAll(",","") ) );
                    }
                    catch ( NumberFormatException e )
                    {
                        log( "**WARNING**Got exception for Price for the row:" + (rowCount+1) + ":" + e.getMessage() );
                    }
                }
                if ( noOfCells > 25 && cells[25].getContents() != null ) //Extended Price
                {
                    try
                    {
                        lineItem.setTotalAmt ( Double.parseDouble( cells[25].getContents().trim().replaceAll("\"", "" ).replaceAll("\\$","").replaceAll(",","") ) );
                    }
                    catch ( NumberFormatException e )
                    {
                        log( "**WARNING**Got exception for Extended Price for the row:" + (rowCount+1) + ":" + e.getMessage() );
                    }
                }
                if ( noOfCells > 26 && cells[26].getContents() != null ) //List Price
                {
                    //IGNORE
                }
                if ( noOfCells > 27 && cells[27].getContents() != null ) //Salesrep
                {
                    lineItem.setSalesPerson( cells[27].getContents().trim() );
                }
                if ( noOfCells > 28 && cells[28].getContents() != null ) //Customer Number
                {
                    lineItem.setCustomerNumber( cells[28].getContents().trim() );
                }
                if ( noOfCells > 29 && cells[29].getContents() != null ) //Cust PO Number
                {
                    lineItem.setCustomerPO( cells[29].getContents().trim() );
                }
                if ( noOfCells > 30 && cells[30].getContents() != null ) //Cust PO Line
                {
                    try
                    {
                        lineItem.setInternalPOLineNo( Integer.parseInt( cells[30].getContents().trim() ) );
                    }
                    catch ( NumberFormatException e )
                    {
                        log( "**WARNING**Got exception for customer po line no for the row:" + (rowCount+1) + ":" + e.getMessage() );
                        lineItem.setInternalPOLineNo(1);
                    }
                }
                else
                {
                    lineItem.setInternalPOLineNo(1);
                }
                if ( noOfCells > 31 && cells[31].getContents() != null ) //Customer Item
                {
                    lineItem.setCustomerPartNo( cells[31].getContents().trim() );
                }
                if ( noOfCells > 32 && cells[32].getContents() != null ) //Freight Terms Code
                {
                    lineItem.setDeliveryTerms( cells[31].getContents().trim() );
                }
                if ( noOfCells > 33 && cells[33].getContents() != null ) //Sales Region
                {
                    lineItem.setRegion( cells[33].getContents().trim() );
                }
                if ( noOfCells > 34 && cells[34].getContents() != null ) //Sales Cntry
                {
                    //IGNORE
                }if ( noOfCells > 35 && cells[35].getContents() != null ) //Profile Class
                {
                    //IGNORE
                }if ( noOfCells > 36 && cells[36].getContents() != null ) //Profile Desc
                {
                    //IGNORE
                }if ( noOfCells > 37 && cells[37].getContents() != null ) //Credit Check
                {
                    //IGNORE
                }
                Address shipAddress = new Address();
                shipAddress.setAddress1( lineItem.getCustomerName() );
                lineItem.setShipAddress( shipAddress );
                if ( noOfCells > 38 && cells[38].getContents() != null ) //Ship Address1
                {
                    shipAddress.setAddress2(cells[38].getContents().trim() );
                }
                if ( noOfCells > 39 && cells[39].getContents() != null ) //Ship Address2
                {
                    shipAddress.setAddress3(cells[39].getContents().trim() );
                }
                if ( noOfCells > 40 && cells[40].getContents() != null ) //Ship City
                {
                    shipAddress.setCity( cells[40].getContents().trim() );
                }
                if ( noOfCells > 41 && cells[41].getContents() != null ) //Ship State
                {
                    shipAddress.setState( cells[41].getContents().trim() );
                }
                if ( noOfCells > 42 && cells[42].getContents() != null ) //Ship Postal Code
                {
                    shipAddress.setZipCode( cells[42].getContents().trim() );
                }
                if ( noOfCells > 43 && cells[43].getContents() != null ) //Ship Cntry
                {
                    shipAddress.setCountry( cells[43].getContents().trim() );
                }
                if ( noOfCells > 44 && cells[44].getContents() != null ) //Ship Primary
                {
                    shipAddress.setDefaultAddress( "Y".equalsIgnoreCase( cells[44].getContents().trim() ) ? "true" : "false" );
                }
                if ( noOfCells > 45 && cells[45].getContents() != null ) //Item Desc
                {
                    lineItem.setDescription( cells[45].getContents().trim() );
                }
                if ( noOfCells > 61 && cells[61].getContents() != null ) //Ship Method
                {
                    lineItem.setShippingMethod( cells[61].getContents().trim() );
                }
                if ( noOfCells > 62 && cells[62].getContents() != null ) //Payment Term
                {
                    lineItem.setPaymentTerms( cells[62].getContents().trim() );
                }
                if ( noOfCells > 69 && cells[69].getContents() != null ) //Payment Term
                {
                    try
                    {
                        lineItem.setLineItemNo( Integer.parseInt( cells[69].getContents().trim() ) );
                    }
                    catch ( NumberFormatException e )
                    {
                        lineItem.setPortingStatusCode( -1 );
                        lineItem.setRemarks( lineItem.getRemarks() + " unable to parse <B>Line Item Number</B> :"+ e.getMessage()+ "<BR>" );
                        log( "**NOT PORTED** Line Item Number for the row:" + (rowCount+1)  + ":" + e.getMessage() );
                        //continue;
                    }
                }
                if ( noOfCells > 70 && cells[70].getContents() != null ) //Payment Term
                {
                    try
                    {
                        lineItem.setShipmentNo( Integer.parseInt( cells[70].getContents().trim() ) );
                    }
                    catch ( NumberFormatException e )
                    {
                        lineItem.setPortingStatusCode( -1 );
                        lineItem.setRemarks( lineItem.getRemarks() + " unable to parse <B>Line Item Shipment Number</B> :"+ e.getMessage()+ "<BR>" );
                        log( "**NOT PORTED** Line Item Shipment Number for the row:" + (rowCount+1)  + ":" + e.getMessage() );
                        //continue;
                    }
                }

            }
            if ( PENDING_SHIPMENT_SHEET.equalsIgnoreCase(sheetName ) )
            {
                lineItem.setPendingShipment( true );
            }
            lineItemList.add(lineItem);
        }
        return lineItemList;
    }
    public static void main ( String[] args ) throws Exception
    {
        SalesParser salesParser = new SalesParser();
        salesParser.execute();
    }

    private String preparePortingStatusMsg ( ArrayList<SalesParserLineItem> lineItemList )
    {
        SalesParserLineItem lineItem   = null;
        StringBuffer statusMsg  = new StringBuffer();
        int lineItemListSize = ( lineItemList == null ) ? 0 : lineItemList.size();
        int count               = 0;
        for ( int i = 0; i < lineItemListSize; i++ )
        {
            lineItem = (SalesParserLineItem)lineItemList.get( i );

            if ( StringUtils.isNullOrEmpty( lineItem.getSalesOrderNo() ) && StringUtils.isNullOrEmpty( lineItem.getCustomerName() ) && lineItem.getLineItemNo() == 0 && lineItem.getShipmentNo() == 0 )
            {
                continue;
            }
            statusMsg.append ( "<TR BGCOLOR='#E6E6E6' ALIGN='CENTER'>" );
            statusMsg.append ( "<TD ALIGN='LEFT'>" ).append ( ++count ).append ( "</TD>" );
            statusMsg.append ( "<TD ALIGN='LEFT'>" ).append ( lineItem.getSalesOrderNo() ).append ( "</TD>" );
            statusMsg.append ( "<TD ALIGN='LEFT'>" ).append ( lineItem.getCustomerName() ).append ( "</TD>" );
            statusMsg.append ( "<TD ALIGN='CENTER'>" ).append ( lineItem.getLineItemNo() ).append ( "</TD>" );
            statusMsg.append ( "<TD ALIGN='CENTER'>" ).append ( lineItem.getShipmentNo() ).append ( "</TD>" );
            statusMsg.append ( "<TD ALIGN='CENTER'>" ).append ( lineItem.getManufacturingPartNo() ).append ( "</TD>" );
            switch( lineItem.getPortingStatusCode() )
            {
                case 0: //'SUCCESS' NEW LINE ITEM CREATED
                    statusMsg.append ( "<TD ALIGN='CENTER'>TRUE</TD>" );
                    statusMsg.append ( "<TD ALIGN='LEFT'>New Line Item <B>created</B> successfully</TD>" );
                    break;
                case 1: //'SUCCESS' LINE ITEM UPDATED
                    statusMsg.append ( "<TD ALIGN='CENTER'>TRUE</TD>" );
                    statusMsg.append ( "<TD ALIGN='LEFT'>Line Item <B>updated</B> successfully</TD>" );
                    break;
                case 2: //'FAILED' SALES ORDER NUMBER REQUIRED
                    statusMsg.append ( "<TD ALIGN='CENTER'><B>FALSE</B></TD>" );
                    statusMsg.append ( "<TD ALIGN='LEFT'>Sales Order number required</TD>" );
                    break;
                case 3: //'FAILED' NO FG PART NUMBER FOUND
                    statusMsg.append ( "<TD ALIGN='CENTER'><B>FALSE</B></TD>" );
                    statusMsg.append ( "<TD ALIGN='LEFT'><B>Product</B> not found in the system</TD>" );
                    break;
                case 4: //'FAILED' CUSTOMER NOT FOUND
                    statusMsg.append ( "<TD ALIGN='CENTER'><B>FALSE</B></TD>" );
                    statusMsg.append ( "<TD ALIGN='LEFT'><B>Customer</B> not found in the system</TD>" );
                    break;
                case -1: //'FAILED' At the time of parsing
                    statusMsg.append ( "<TD ALIGN='CENTER'><B>FALSE</B></TD>" );
                    statusMsg.append ( "<TD ALIGN='LEFT'>"+ lineItem.getRemarks() + "</TD>" );
                    break;
            }
            statusMsg.append ( "</TR>");
        }
        return statusMsg.toString();
    }

    private void sendSalesOrdersPortingAlert ( String subject, String message, String fromEmail, String notificationType ) throws Exception
    {
        ArrayList<String> emailList = NotificationDAO.loadEmailsByNotificationType(null, notificationType );
        String[] emails = new String[emailList.size()];
        emailList.toArray(emails);
        String toEmails = StringUtils.concatStrings( emails, Mailer.EMAIL_SEPARATOR );
        log ( "Sending alerts to: " + toEmails );
        System.out.println ( "Sending alerts to: " + toEmails );
        try
        {
            Mailer.send ( fromEmail, toEmails, null, null, subject, message, null, Mailer.CONTENT_TYPE_HTML, false );
        }
        catch( Exception e )
        {
            throw new ScalarException ( "Could not send email notifications", e );
        }
    }

    private void parseShippingFile( File shipFile, String sheetName ) throws Exception
    {
        if ( shipFile == null ) throw new ScalarException( "Filename argument is null" );

        ParserResult result     = null;
        String soNumber         = null;
        String packingSlipNo    = null;
        String lineNo           = null;
        SalesParserLineItem lineItem        = null;
        try
        {
            log( "\nFound " + shipFile.getName() );
            log( "\nParsing the file for Shipment Report..." );
            System.out.println( "\nFound " + shipFile.getName() );
            System.out.println( "\nParsing the file for Shipment Report..." );

            Parser parser = ParserFactory.getXLSParser( shipFile , sheetName );

            ((XLSParserImpl)parser).setHeaderRowIdx(2);
            ((XLSParserImpl)parser).setDataStartRowIdx(3);

            result = parser.parse();
            //System.out.println("result.getHeadings() = " + result.getHeadings());
           
            while ( result.next() )
            {
                if ( "Shipment report".equalsIgnoreCase( sheetName ) )
                {
                    soNumber = result.getString("Order Num");
                    packingSlipNo  = result.getString("Ship Request Number");
                    lineNo = result.getString("Line Num");
                }
                else
                {
                    soNumber = result.getString("Sales Order");
                    packingSlipNo  = result.getString("Ship Req No");
                    lineNo = result.getString("Order Line");
                }
                
                int lineItemNo = -1;
                int shipmentNo = -1;
                if ( soNumber != null && soNumber.trim().length() > 0 && packingSlipNo != null && packingSlipNo.trim().length() >0
                        && lineNo != null && lineNo.trim().length() > 0 )
                {
                    lineItemNo = Integer.parseInt( lineNo.split("\\.")[0] );
                    shipmentNo = Integer.parseInt( lineNo.split("\\.")[1] );

                    if ( soNumber.indexOf(".") != -1 )
                    {
                        soNumber = soNumber.substring(0, soNumber.indexOf(".") );
                    }
                    if ( packingSlipNo.indexOf(".") != -1 )
                    {
                        packingSlipNo = packingSlipNo.substring(0, packingSlipNo.indexOf("."));
                    }
                    /*if ( lineNo.indexOf(".") != -1 )
                    {
                        lineItemNo = Integer.parseInt( lineNo.substring(0, lineNo.indexOf(".")) );
                        if ( lineNo.lastIndexOf(".") != -1 && lineNo.lastIndexOf(".") > 2 )
                        {                               
                            shipmentNo = Integer.parseInt( lineNo.substring(lineNo.indexOf(".") + 1, lineNo.lastIndexOf(".")) );
                        }
                        else
                        {
                            shipmentNo = Integer.parseInt( lineNo.substring(lineNo.indexOf(".") + 1 ) );
                        }

                    }
                    else
                    {
                        lineItemNo = Integer.parseInt( lineNo );
                    }*/
                                        
                    lineItem = (SalesParserLineItem) m_lineItemMap.get( soNumber + packingSlipNo + lineItemNo + shipmentNo );
                    if (lineItem == null)
                    {
                        lineItem = new SalesParserLineItem();
                        m_lineItemMap.put(soNumber + packingSlipNo + lineItemNo + shipmentNo, lineItem);
                    }
                    int quantity = 0;

                    if ( lineItem.getShippedQty() > 0 )
                    {
                         quantity = lineItem.getShippedQty();

                         try
                         {
                             if ( "Shipment report".equalsIgnoreCase( sheetName ) )
                             {
                                if ( result.getString("Ship Quantity") != null && result.getString("Ship Quantity").indexOf(".") != -1)
                                {
                                    quantity += Integer.parseInt( result.getString("Ship Quantity").substring(0, result.getString("Ship Quantity").indexOf(".")).replaceAll(",","") );
                                }
                                else
                                {
                                    quantity += Integer.parseInt( result.getString("Ship Quantity").replaceAll(",","") );
                                }
                             }
                             else
                             {

                                if ( result.getString("Backlog Qty") != null && result.getString("Backlog Qty").indexOf(".") != -1)
                                {
                                    quantity += Integer.parseInt( result.getString("Backlog Qty").substring(0, result.getString("Backlog Qty").indexOf(".")).replaceAll(",","") );
                                }
                                else
                                {
                                    quantity += Integer.parseInt( result.getString("Backlog Qty").replaceAll(",","") );
                                }
                             }
                           
                         }
                         catch (NumberFormatException nfe)
                         {                                
                             quantity = 0;
                         }

                    }
                    else
                    {    try
                         {
                             if ("Shipment report".equalsIgnoreCase( sheetName ) )
                             {
                                if ( result.getString("Ship Quantity") != null && result.getString("Ship Quantity").indexOf(".") != -1 )
                                {
                                    quantity = Integer.parseInt( result.getString("Ship Quantity").substring(0, result.getString("Ship Quantity").indexOf(".")).replaceAll(",","") );
                                }
                                else
                                {
                                    quantity = Integer.parseInt( result.getString("Ship Quantity").replaceAll(",","") );
                                }
                             }
                             else
                             {
                                if ( result.getString("Backlog Qty") != null && result.getString("Backlog Qty").indexOf(".") != -1 )
                                {
                                    quantity = Integer.parseInt( result.getString("Backlog Qty").substring(0, result.getString("Backlog Qty").indexOf(".")).replaceAll(",","") );
                                }
                                else
                                {
                                    quantity = Integer.parseInt( result.getString("Backlog Qty").replaceAll(",","") );
                                }
                             }
                         }
                         catch (NumberFormatException nfe)
                         {
                             quantity = 0;
                         }
                    }

                    if ( quantity > 0 )
                    {
                        lineItem.setSalesOrderNo( soNumber);
                        lineItem.setCustomerName( result.getString("Customer Name") );
                        lineItem.setCustomerPO( result.getString("Cust Po Number") );
                        lineItem.setManufacturingPartNo( result.getString( "Ship Item" ) );
                        lineItem.setUnitPrice( Float.parseFloat( result.getString("Unit Selling Price").trim().replaceAll("\"", "" ).replaceAll("\\$","").replaceAll(",","") ) );
                        
                        lineItem.setBookingDate( StringUtils.formatDate( result.getString("Ordered Date"), "dd-MMM-yyyy", "MM/dd/yyyy") );
                        lineItem.setScheduleDate( StringUtils.formatDate( result.getString("Promise Date"), "dd-MMM-yyyy", "MM/dd/yyyy") );
                        lineItem.setRevisedScheduleDate( StringUtils.formatDate( result.getString("Schd Ship Date"), "dd-MMM-yyyy", "MM/dd/yyyy") );
                        lineItem.setRequestedDockDate( StringUtils.formatDate( result.getString("Request Date"), "dd-MMM-yyyy", "MM/dd/yyyy") );
                        lineItem.setShippedDate( StringUtils.formatDate( result.getString("Ship Date"), "dd-MMM-yyyy", "MM/dd/yyyy") );
                        lineItem.setLabel( packingSlipNo);
                        lineItem.setLineItemNo( lineItemNo);
                        if( result.getString("Customer Name") != null && "STD".equalsIgnoreCase( result.getString("Customer Name").trim() ) )
                        {
                        	lineItem.setIsSample( 1 );
                        }

                        lineItem.setShipmentNo( shipmentNo );
                        lineItem.setShippedQty( quantity );
                        lineItem.setStatus("SHIPPED");

                        if ( "Shipment report".equalsIgnoreCase( sheetName ) )
                        {
                            lineItem.setShippedDate( StringUtils.formatDateToString(result.getDate("Ship Date") ) );
                            if ( "UTC".equalsIgnoreCase( result.getString("Ship From Org") ))
                            {
                                lineItem.setShippingLocation( VENDOR_NAME_UTAC );
                            }
                            else if ("ASC".equalsIgnoreCase( result.getString("Ship From Org") ))
                            {
                                lineItem.setShippingLocation( VENDOR_NAME_IKANOS_ASECL );
                            }
                            else if ("AST".equalsIgnoreCase( result.getString("Ship From Org") ))
                            {
                                lineItem.setShippingLocation( VENDOR_NAME_ASET );
                            }
                            else if ("US1".equalsIgnoreCase( result.getString("Ship From Org") ) )
                            {
                                lineItem.setShippingLocation( "US1-Fremont" );
                            }
                            else
                            {
                                lineItem.setShippingLocation( result.getString("Ship From Org") );
                            }
                        }
                        else
                        {
                            lineItem.setShippedDate( StringUtils.formatDateToString(result.getDate("Schd Ship Date") ) );
                            if ( "UTC".equalsIgnoreCase( result.getString("Ship From Location") ))
                            {
                                lineItem.setShippingLocation( VENDOR_NAME_UTAC );
                            }
                            else if ("ASC".equalsIgnoreCase( result.getString("Ship From Location") ))
                            {
                                lineItem.setShippingLocation( VENDOR_NAME_IKANOS_ASECL );
                            }
                            else if ("AST".equalsIgnoreCase( result.getString("Ship From Location") ))
                            {
                                lineItem.setShippingLocation( VENDOR_NAME_ASET );
                            }
                            else if ("US1".equalsIgnoreCase( result.getString("Ship From Location") ) )
                            {
                                lineItem.setShippingLocation( "US1-Fremont" );
                            }
                            else
                            {
                                lineItem.setShippingLocation( result.getString("Ship From Location") );
                            }
                        }
                    }
                }
            }
        }
        catch ( Exception ex )
        {
            System.out.println("ERROR!!!ERROR!!! While Parsing the Shipment Rpeort");
            ex.printStackTrace();
            throw ex;
        }

    }// end of prase Shipment Report method

    /*private ArrayList parseSalesCSVFile ( File salesPortingFile ) throws IOException
    {
        int rowNo = 0;
        int noOfColumns = 0;
        String[] columns = null;
        String delim = "\t";
        ArrayList lineItemList      = new ArrayList();
        SalesParserLineItem lineItem = null;
        String columnValue = null;
        BufferedReader br = new BufferedReader( new FileReader( salesPortingFile ) );
        String line = null;
        while ( ( line = br.readLine() ) != null && line.length() > 0 )
        {
            rowNo++;
            line = line.replaceAll( "\"", "\\\"" );
            columns = line.split(delim);
            noOfColumns = columns.length;
            if ( noOfColumns >= 36 )
            {
                lineItem = new SalesParserLineItem();
                for (int x = 0; x < noOfColumns; x++)
                {
                    columnValue = columns[x] == null ? null : columns[x].trim();
                    switch (x)
                    {
                       case 0: //ORDER_NO
                           lineItem.setSalesOrderNo( columnValue );
                           break;
                       case 1: //LINE_NO
                           try
                           {
                                lineItem.setLineItemNo( Integer.parseInt ( columnValue ) );
                           }catch( NumberFormatException ignore ) { log("ROW:" + rowNo + ": NUMBER FORMAT EXCEPTION FOR THE LINEITEM NUMBER:" + columnValue );}
                           break;
                       case 2: //REL_NO
                           try
                           {
                               lineItem.setReleaseNo( Integer.parseInt ( columnValue ) );
                           }catch( NumberFormatException ignore ) { log("ROW:" + rowNo + ": NUMBER FORMAT EXCEPTION FOR THE LINEITEM RELEASE NUMBER:" + columnValue ); }
                           break;
                       case 3: //LINE_STATE
                           if ( "Invoiced/Closed".equalsIgnoreCase( columnValue ) )
                           {
                               lineItem.setStatus( "SHIPPED" );
                           }
                           else if ( "Released".equalsIgnoreCase( columnValue ) || "Reserved".equalsIgnoreCase( columnValue ))
                           {
                               lineItem.setStatus( "APPROVED" );
                           }
                           else if ( "Partially Delivered".equalsIgnoreCase( columnValue ) )
                           {
                               lineItem.setStatus( "PARTIALLY SHIPPED" );
                           }
                           else
                           {
                               lineItem.setStatus(columnValue);
                           }
                           break;
                       case 4: //AUTHORIZE_CODE
                           lineItem.setAddedById( columnValue );
                           break;
                       case 5: //ORDER_ID
                           //IGNORE..
                           break;
                       case 6: //CUSTOMER_NO
                           lineItem.setCustomerNumber( columnValue );
                           break;
                       case 7: //CUSTOMER_NAME
                           lineItem.setCustomerName( columnValue );
                           break;
                       case 8: //CUSTOMER_PO_NO
                           lineItem.setCustomerPO( columnValue );
                           break;
                       case 9: //CONTRACT
                           lineItem.setContract( columnValue );
                           break;
                       case 10: //CATALOG_NO
                           lineItem.setMarketingPartNo( columnValue );
                           break;
                       case 11: //CATALOG_DESC
                           lineItem.setDescription( columnValue );
                           break;
                       case 12: //PART_NO
                           //IGNORE..
                           break;
                       case 13: //PURCHASE_PART_NO
                           lineItem.setManufacturingPartNo( columnValue );
                           break;
                       case 14: //SUPPLY_CODE
                           //IGNORE...
                           break;
                       case 15: //BUY_QTY_DUE
                           try
                           {
                                lineItem.setQuantity( Integer.parseInt( columnValue ) );
                           } catch ( NumberFormatException ignore ) { log("ROW:" + rowNo + ": NUMBER FORMAT EXCEPTION FOR THE LINEITEM QTY:" + columnValue ); }
                           break;
                       case 16: //QTY_SHIPPED
                           try
                           {
                               lineItem.setShippedQty( Integer.parseInt ( columnValue ));
                           } catch( NumberFormatException ignore ) { log("ROW:" + rowNo + ": NUMBER FORMAT EXCEPTION FOR THE LINEITEM SHIPPED QTY:" + columnValue ); }
                           break;
                       case 17: //QTY_RETURNED
                           //IGNORE...NEED CLARIFICATION..
                           break;
                       case 18: //SALES_UNIT_MEAS
                           //IGNORE..
                           break;
                       case 19: //SALE_UNIT_PRICE
                           try
                           {
                               lineItem.setUnitPrice( Float.parseFloat( columnValue ) );
                           } catch ( NumberFormatException ignore ) { log("ROW:" + rowNo + ": NUMBER FORMAT EXCEPTION FOR THE LINEITEM UNIT PRICE:" + columnValue ); }
                           break;
                       case 20: //CURRENCY_CODE
                           //IGNORE..
                           break;
                       case 21: //WANTED_DELIVERY_DATE
                           lineItem.setRequestedDockDate( StringUtils.formatDate( columnValue, "dd-MMM-yy", "MM-dd-yyyy" ) );
                           break;
                       case 22: //PROMISED_DELIVERY_DATE
                           lineItem.setScheduleDate( StringUtils.formatDate( columnValue != null && columnValue.length() == 0 ? null : columnValue, "dd-MMM-yy", "MM-dd-yyyy" ) );
                           break;
                       case 23: //REAL_SHIP_DATE
                           lineItem.setShippedDate( StringUtils.formatDate( columnValue != null && columnValue.length() == 0 ? null : columnValue, "dd-MMM-yy", "MM-dd-yyyy" ) );
                           break;
                       case 24: //DATE_ENTERED
                           lineItem.setBookingDate( StringUtils.formatDate( columnValue, "dd-MMM-yy", "MM-dd-yyyy" ) );
                           break;
                       case 25: //CUSTOMER_PART_NO
                           lineItem.setCustomerPartNo( columnValue );
                           break;
                       case 26: //ORIGINAL_PART_NO
                           //IGNORE..
                           break;
                       case 27: //SHIP_VIA_CODE
                           //IGNORE..
                           break;
                       case 28: //SHIP_VIA_DESC
                           lineItem.setShippingMethod( columnValue );
                           break;
                       case 29: //DELIVERY_TERMS
                           //IGNORE..
                           break;
                       case 30: //DELIVERY_TERMS_DESC
                           lineItem.setDeliveryTerms( columnValue );
                           break;
                       case 31: //REGION_CODE
                           lineItem.setRegion( columnValue );
                           break;
                       case 32: //INT_PO_NO
                           lineItem.setInternalPONo( columnValue );
                           break;
                       case 33: //INT_POLINE_NO
                           try
                           {
                                lineItem.setInternalPOLineNo( Integer.parseInt ( columnValue ) );
                           } catch ( NumberFormatException ignore ) { log("ROW:" + rowNo + ": NUMBER FORMAT EXCEPTION FOR THE INTERNAL PO LINE NO:" + columnValue ); }
                           break;
                       case 34: //INT_POREL_NO
                           try
                           {
                                lineItem.setInternalReleaseNo( Integer.parseInt ( columnValue ) );
                           } catch ( NumberFormatException ignore ) { log("ROW:" + rowNo + ": NUMBER FORMAT EXCEPTION FOR THE INTERNAL RELEASE NO:" + columnValue ); }
                           break;
                       case 35: //INT_ORDER_NO
                           lineItem.setInternalSONo( columnValue );
                           break;
                    }
                }
                lineItemList.add ( lineItem );
            }
            else
            {
                log ( "IGNORED Row:" + rowNo + ": HAS NOT ENOUGH FIELDS" );
            }
        }
        try
        {
            br.close();
        }catch (IOException ignore) {}
        return lineItemList;
    }*/
}

